package hbnu.stockManage.repository;

import hbnu.stockManage.model.Product;
import hbnu.stockManage.util.Db;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class ProductRepository {

    public List<Product> findAll() {
        String sql = "select id, name, price, stock from product order by id";
        List<Product> list = new ArrayList<>();
        try (Connection c = Db.getConnection();
             PreparedStatement ps = c.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(mapRow(rs));
            }
        } catch (SQLException e) {
            System.out.println("查询全部失败: " + e.getMessage());
        }
        return list;
    }

    public Optional<Product> findById(int id) {
        String sql = "select id, name, price, stock from product where id = ?";
        try (Connection c = Db.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }
            }
        } catch (SQLException e) {
            System.out.println("按ID查询失败: " + e.getMessage());
        }
        return Optional.empty();
    }

    public List<Product> findByNameLike(String keyword) {
        String sql = "select id, name, price, stock from product where lower(name) like ? order by id";
        List<Product> list = new ArrayList<>();
        try (Connection c = Db.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, "%" + (keyword == null ? "" : keyword.toLowerCase()) + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) list.add(mapRow(rs));
            }
        } catch (SQLException e) {
            System.out.println("按名称查询失败: " + e.getMessage());
        }
        return list;
    }

    public Product add(Product product) {
        String sql = "insert into product(name, price, stock) values(?, ?, ?)";
        try (Connection c = Db.getConnection();
             PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, product.getName());
            ps.setDouble(2, product.getPrice());
            ps.setInt(3, product.getStock());
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) {
                    product.setId(keys.getInt(1));
                }
            }
        } catch (SQLException e) {
            System.out.println("新增失败: " + e.getMessage());
        }
        return product;
    }

    public boolean update(int id, String name, Double price, Integer stock) {
        StringBuilder sb = new StringBuilder("update product set ");
        List<Object> params = new ArrayList<>();
        if (name != null && !name.isBlank()) { sb.append("name = ?, "); params.add(name); }
        if (price != null && price >= 0) { sb.append("price = ?, "); params.add(price); }
        if (stock != null && stock >= 0) { sb.append("stock = ?, "); params.add(stock); }
        if (params.isEmpty()) return true; // 无改动视为成功
        sb.setLength(sb.length() - 2);
        sb.append(" where id = ?");
        params.add(id);
        try (Connection c = Db.getConnection();
             PreparedStatement ps = c.prepareStatement(sb.toString())) {
            for (int i = 0; i < params.size(); i++) ps.setObject(i + 1, params.get(i));
            int n = ps.executeUpdate();
            return n > 0;
        } catch (SQLException e) {
            System.out.println("更新失败: " + e.getMessage());
            return false;
        }
    }

    public boolean remove(int id) {
        String sql = "delete from product where id = ?";
        try (Connection c = Db.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            System.out.println("删除失败: " + e.getMessage());
            return false;
        }
    }

    // JDBC 模式下不需要save/nextId，保留空实现以兼容现有调用
    public int nextId() { return 0; }
    public void save() { }

    private Product mapRow(ResultSet rs) throws SQLException {
        return new Product(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getDouble("price"),
                rs.getInt("stock")
        );
    }
}
